Re: [INTERFACES] JDBC next() method - Mailing list pgsql-interfaces
From | Herouth Maoz |
---|---|
Subject | Re: [INTERFACES] JDBC next() method |
Date | |
Msg-id | l03130302b348fb9e81c6@[147.233.159.109] Whole thread Raw |
In response to | Re: [INTERFACES] JDBC next() method (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-interfaces |
At 19:40 +0300 on 25/04/1999, Tom Lane wrote: > Well, this is certainly adequate precedent for the behavior of these > particular aggregates --- although I'd have to say that the standard- > writers blew it for SUM; SUM of an empty set ought to return 0 not > null. (It looks like Postgres follows the spec, however.) I don't agree. Suppose you want to sum of all your banking transactions in January. There is a distinction between getting a 0, meaning you had a balanced budget in January, and getting a null, meaning you made no transactions in January. > Now that I think about it, the arguments on the hackers list were not > about the plain SELECT case but about the GROUP BY case. For example, > if you do > > SELECT productname, AVG(saleprice) FROM sales GROUP BY productname; > > then you get a row in the output for each different productname, and > a separate instance of AVG is run over the prices for each group. > (Unless there are NULLs in the saleprice column, none of the AVG > instances could ever return a null result.) > > BUT: what happens if the sales table is empty? There are no > productnames, therefore no groups, therefore no rows ought to appear > in the output (IMHO). However, what Postgres actually does right now > is to emit one all-nulls row (but only if an aggregate function was > used; if you say "SELECT productname FROM sales GROUP BY productname" > then you get no rows). That is the behavior that we've gone 'round and > 'round on without any resolution; it seems obviously inconsistent to me, > but others think it's OK because it parallels what happens in the non- > GROUP BY case. > > Is there anything in the SQL92 spec addressing this point? Ooh, definitely... Here are the general rules regarding a query expression (i.e. a general select statement). A grouped table is defined somewhere else in the document as the result of a group by or having clause. You really should read the definitions (I can make my draft available on the web for a while. I don't remember where I downloaded it). <<< Begin quotation >>> General Rules 1) Case: a) If T is not a grouped table, then Case: i) If the <select list> contains a <set function specifica- tion> that contains a reference to a column of T or di-rectly contains a <set function specification> that does not contain an outer reference, then T is the argument or argumentsource of each such <set function specification> and the result of the <query specification> is a table con- sistingof 1 row. The i-th value of the row is the value specified by the i-th <value expression>. ii) If the <select list> does not include a <set function spec- ification> that contains a reference to T, then each<value expression> is applied to each row of T yielding a table of M rows, where M is the cardinality of T. The i-thcolumn of the table contains the values derived by the evaluation of the i-th <value expression>. Case: 1) If the <set quantifier> DISTINCT is not specified, then the table is the result of the <query specification>. 2) If the <set quantifier> DISTINCT is specified, then the result of the <query specification> is the table derived from that table by the elimination of any redundant duplicate rows. b) If T is a grouped table, then Case: i) If T has 0 groups, then the result of the <query specifica- tion> is an empty table. ii) If T has one or more groups, then each <value expression> is applied to each group of T yielding a table of M rows,where M is the number of groups in T. The i-th column of the table contains the values derived by the evaluation ofthe i-th <value expression>. When a <value expression> is applied to a given group of T, that group is the argument orargument source of each <set function specification> in the <value expression>. Case: 1) If the <set quantifier> DISTINCT is not specified, then the table is the result of the <query specification>. 2) If the <set quantifier> DISTINCT is specified, then the result of the <query specification> is the table derived from T by the elimination of any redundant duplicate rows. <<< End quotation >>> Which implies exactly what you say. Perhaps you should forward this to the Hackers list to re-start the argument - I am not a subscriber there. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
pgsql-interfaces by date: